# -*- coding: utf-8 -*-
import pymssql

# 数据库连接信息
DB_CONFIG = {
    'server': '(local)',
    'user': 'sa',
    'password': '123456789',
    'database': 'JCDGXT',
    'charset': 'utf8'
}

# 初始化数据库连接
def get_db_connection():
    return pymssql.connect(
        server=DB_CONFIG['server'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        charset=DB_CONFIG['charset']
    )

# 获取所有学生信息
def all():
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT sno, sname, subject FROM [students$]')
    result = cursor.fetchall()
    conn.close()

    # 手动进行编码转换
    for row in result:
        row['sname'] = row['sname'].encode('latin1').decode('gbk')
        row['subject'] = row['subject'].encode('latin1').decode('gbk')
    return result

# 插入学生信息
def insert_students(sno, sname, subject):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('INSERT INTO [students$] (sno, sname, subject) VALUES (%s, %s, %s)', (sno, sname, subject))
    conn.commit()
    conn.close()

def students_exists(sno):
    students = all()
    return any(student['sno'] == sno for student in students)

# 根据学生号查询信息
def query_by_sno(sno):
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT sname, subject FROM [students$] WHERE sno = %s', (sno,))
    result = cursor.fetchone()
    conn.close()
    if result:
        result['sname'] = result['sname'].encode('latin1').decode('gbk')
        result['subject'] = result['subject'].encode('latin1').decode('gbk')
    return result

# 根据学生号更新信息
def update_by_sno(sno, data):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE [students$] SET sname=%s, subject=%s WHERE sno=%s', (data['sname'], data['subject'], sno))
    conn.commit()
    conn.close()

# 根据学生号删除信息
def delete_by_sno(sno):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM [students$] WHERE sno = %s', (sno,))
    conn.commit()
    conn.close()
